package dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Hashtable;

import orm.CollectivityClass;
import orm.IndividualClass;
import orm.IndividualTeachingPlan;
import orm.Teacher;
import orm.Users;
import util.DBHelper;

public class TeacherDAO {
	Connection conn = DBHelper.getConnection();
	Statement stmt;
	ResultSet rs;
	
	public Hashtable<String, String> getTeachersToClassID() throws SQLException {
		Hashtable<String, String> ht = new Hashtable<String, String>();
		stmt = conn.createStatement();
//		rs = stmt.executeQuery("select StudentID, StudentName from Student where ClassID = '" +
//				"(select ClassID from Teacher where UserName = '" + userName + "')" + "'");
		rs = stmt.executeQuery("select TeacherID, TeacherName from teacher");
		
		while (rs.next()) {
			ht.put(rs.getString(1), rs.getString(2));
		}
		
		return ht;
	}
	
	public Teacher getTeacherID(String teacherID) throws SQLException {
		Teacher teacher = new Teacher();
		stmt = conn.createStatement();
		rs = stmt.executeQuery("select * from teacher where TeacherID ='" + teacherID + "'");
		while (rs.next()) {
			int i = 1;
			teacher.setTeacherID(rs.getInt(i++));
			teacher.setUserID(rs.getInt(i++));
			teacher.setTeacherName(rs.getString(i++));
			teacher.setSex(rs.getInt(i++));
			teacher.setBirthday(rs.getString(i++));
			teacher.setWorkingYears(rs.getString(i++));
			teacher.setDegree(rs.getInt(i++));
			teacher.setPosition(rs.getString(i++));
			teacher.setDirection(rs.getString(i++));
			teacher.setSpeciality(rs.getString(i++));
			teacher.setTeachingExperience(rs.getString(i++));
			teacher.setGlory(rs.getString(i++));
			teacher.setAssignment(rs.getInt(i++));
			teacher.setLiability(rs.getInt(i++));
			
		}
		
		return teacher;
	}
	public String getTeacherName(String userName) throws SQLException{
		stmt = conn.createStatement();
		String teacherName = null;
		rs = stmt.executeQuery("select TeacherName from teacher where UserID = (select UserID from users where UserName = '" + userName + "')");
		
		while(rs.next()){
			teacherName = rs.getString(1);
		}
		return teacherName;
	}
	public String getTeacherIDByUserName(String userName) throws SQLException{
		stmt = conn.createStatement();
		String teacherID = null;
		rs = stmt.executeQuery("select TeacherID from teacher where UserID = (select UserID from users where UserName = '" + userName + "')");
		
		while(rs.next()){
			teacherID = rs.getString(1);
		}
		return teacherID;
	}
	
	public void insertTeacher(Teacher teacher, Users user) throws SQLException {	
		try{
			stmt = conn.createStatement();
			stmt.execute("insert into users(RoleID, UserName, Pwd) values(1, '" + user.getUserName() + "', '" + 
					user.getPwd() + "')");
			rs = stmt.executeQuery("select max(UserID) from users ");	
			int maxUserID =1;
			while(rs.next()){
				maxUserID = rs.getInt(1);
			}
			stmt.execute("insert into Teacher values('" + teacher.getTeacherID() + "'," + maxUserID + " , '" + teacher.getTeacherName() + "', " + 
					teacher.getSex() + ", '" + teacher.getBirthday() + "', '" +
					teacher.getWorkingYears() + "', '" + teacher.getDegree() + "', '" +
					teacher.getPosition() + "', '" + teacher.getDirection() + "', '" +
					teacher.getSpeciality() + "', '" + teacher.getTeachingExperience() + "', '" + 
					teacher.getGlory() + "', '" + teacher.getAssignment() + "', '" +
					teacher.getLiability() +  "')");
			stmt.close();	
		}catch(Exception e){
			System.out.println(e);
		}finally{
			conn.close();
		}	
	}
	
	public void updateTeacher(Statement stmt, Users user, Teacher teacher) throws SQLException {
		try{
//			stmt = conn.createStatement();
			
			stmt.execute("update users set Pwd = '" + user.getPwd() + "' where UserID = "+ teacher.getUserID() +"");

			stmt.execute("update teacher set TeacherName ='" + teacher.getTeacherName() + 
					"', TeacherID = '" + teacher.getTeacherID() + "', Sex = " + 
					teacher.getSex() + ", Birthday = '" + teacher.getBirthday() + 
					"', WorkingYears = '" + teacher.getWorkingYears() + "', Degree = " + 
					teacher.getDegree() + ", Position = '" + teacher.getPosition() + 
					"', Direction = '" + teacher.getDirection() + "', Speciality = '" + 
					teacher.getSpeciality() + "', TeachingExperience = '" + teacher.getTeachingExperience() + 
					"', Glory = '" + teacher.getGlory() + "', Assignment = " + teacher.getAssignment() + 
					", Liability = " + teacher.getLiability() + " where TeacherID = '" + teacher.getTeacherID() + "'");
			stmt.close();	
		}catch(Exception e){
			System.out.println(e);
		}finally{
			conn.close();
		}
	}
	public void updateCollectivityClass(Statement stmt, CollectivityClass collectivityClass, Teacher teacher) throws SQLException {
		try{
//			stmt = conn.createStatement();
			stmt.execute("update collectivityclass set TeacherName = (select TeacherName from teacher where TeacherID = '" + teacher.getTeacherID() + "') where TeacherID = '" + teacher.getTeacherID() + "'");

			stmt.close();	
		}catch(Exception e){
			System.out.println(e);
		}finally{
			conn.close();
		}
	}
	public void updateIndividualClass(Statement stmt, IndividualClass individualClass, Teacher teacher) throws SQLException {
		try{
			conn = DBHelper.getConnection();
			stmt = conn.createStatement();
//			stmt = conn.createStatement();
			stmt.execute("update individualclass set TeacherName = (select TeacherName from teacher where TeacherID = '" + teacher.getTeacherID() + "') where TeacherID = '" + teacher.getTeacherID() + "'");

			stmt.close();	
		}catch(Exception e){
			System.out.println(e);
		}finally{
			conn.close();
		}
	}
	
	public void deleteTeacher(Statement statement, String teacherID) throws SQLException {
		statement.execute("delete from teacher where TeacherID = '" + teacherID + "'");
	}
	
	public ArrayList<Teacher> getAllTeachers() throws SQLException {
		ArrayList<Teacher> al = new ArrayList<Teacher>();
		conn = DBHelper.getConnection();
		stmt = conn.createStatement();

		rs = stmt.executeQuery("select TeacherID,TeacherName, Sex, Position, Assignment, Liability from teacher ORDER BY TeacherID ASC");
		Teacher teacher = null;
		
		while (rs.next()) {
			teacher = new Teacher();
			teacher.setTeacherID(rs.getInt("teacherID"));
			teacher.setTeacherName(rs.getString("TeacherName"));
			teacher.setSex(rs.getInt("Sex"));
			teacher.setPosition(rs.getString("Position"));
			teacher.setAssignment(rs.getInt("Assignment"));
			teacher.setLiability(rs.getInt("Liability"));
			
			
			al.add(teacher);
		}
		
		stmt.close();
		conn.close();
		
		return al;
	}

	public boolean doesExist(String teacherID) throws SQLException {
		stmt = conn.createStatement();
		rs = stmt.executeQuery("select count(*) from teacher where TeacherID='" + teacherID + "'");
		rs.next();
		return rs.getInt(1) == 0 ? false : true;
	}
}
